<?php
//TODO:Agregar a truuloCRM
/**
 * User: Alex Yepez <ayepez@tradesystem.com.ec>
 * Date: 26/10/2014
 * Time: 10:33
 */
class CDbCustomizeCommandBuilder extends CDbCommandBuilder
{
    const PARAM_PREFIX = ':yp';
    private $_schema;
    private $_connection;

    public function __construct($schema)
    {
        $conecction = new CDbConnection($schema->connectionString, $schema->username, $schema->password);
        $this->_schema = $conecction->schema;
        $this->_connection = $conecction->schema->dbConnection;
    }

    /**
     * @author Qiang Xue <qiang.xue@gmail.com>
     * @return CDbConnection database connection.
     */
    public function getDbConnection()
    {
        return $this->_connection;
    }

    /**
     * @author Qiang Xue <qiang.xue@gmail.com>
     * @return CDbSchema the schema for this command builder.
     */
    public function getSchema()
    {
        return $this->_schema;
    }

    /**
     * @author Alex Yepez <ayepez@tradesystem.com.ec>
     * Creacion de comando de ingresos multiples, agragardo la función de actualizado
     * @param $table_name
     * @param array $values
     * @param array $attr_update_on_duplicate
     */
    public function createMultipleInsertOrUpdateOnDuplicateCommand($table_name, array $values, array $attr_update_on_duplicate = array())
    {
        return $this->composeMultipleInsertOrUpdateOnDuplicateCommand($table_name, $values, $attr_update_on_duplicate);
    }

    /**
     * Compositor del comando para la insercion multiple, adicionalmente, se agraga la funcionalidad de actualizar siertos campos en caso
     * de ingresar un registro duplicado;
     * @param $table
     * @param array $data
     * @param array $attr_uod
     * @param array $templates
     * @return CDbCommand
     * @throws CDbException
     */
    private function composeMultipleInsertOrUpdateOnDuplicateCommand($table, array $data, array $attr_uod = array(), array $templates = array())
    {
        $templates = array_merge(
            array(
                'main' => 'INSERT INTO {{tableName}} ({{columnInsertNames}}) VALUES {{rowInsertValues}}',
                //on duplicate template
                'addTemplate' => ' ON DUPLICATE KEY UPDATE {{columnUODValue}}',
                'columnInsertValue' => '{{value}}',
                'columnInsertValueGlue' => ', ',
                'rowInsertValue' => '({{columnInsertValues}})',
                'rowInsertValueGlue' => ', ',
                'columnInsertNameGlue' => ', ',
            ),
            $templates
        );
        $this->ensureTable($table);
        $tableName = $this->getDbConnection()->quoteTableName($table->name);
        $params = array();
        $columnInsertNames = array();
        $rowInsertValues = array();
        //columns
        $columns = array();
        foreach ($data as $rowData) {
            foreach ($rowData as $columnName => $columnValue) {
                if (!in_array($columnName, $columns, true))
                    if ($table->getColumn($columnName) !== null)
                        $columns[] = $columnName;
            }
        }
        foreach ($columns as $name)
            $columnInsertNames[$name] = $this->getDbConnection()->quoteColumnName($name);
        $columnInsertNamesSqlPart = implode($templates['columnInsertNameGlue'], $columnInsertNames);
        foreach ($data as $rowKey => $rowData) {
            $columnInsertValues = array();
            foreach ($columns as $columnName) {
                $column = $table->getColumn($columnName);
                $columnValue = array_key_exists($columnName, $rowData) ? $rowData[$columnName] : new CDbExpression('NULL');
                if ($columnValue instanceof CDbExpression) {
                    $columnInsertValue = $columnValue->expression;
                    foreach ($columnValue->params as $columnValueParamName => $columnValueParam)
                        $params[$columnValueParamName] = $columnValueParam;
                } else {
                    $columnInsertValue = ':' . $columnName . '_' . $rowKey;
                    $params[':' . $columnName . '_' . $rowKey] = $column->typecast($columnValue);
                }
                $columnInsertValues[] = strtr($templates['columnInsertValue'], array(
                    '{{column}}' => $columnInsertNames[$columnName],
                    '{{value}}' => $columnInsertValue,
                ));
            }
            $rowInsertValues[] = strtr($templates['rowInsertValue'], array(
                '{{tableName}}' => $tableName,
                '{{columnInsertNames}}' => $columnInsertNamesSqlPart,
                '{{columnInsertValues}}' => implode($templates['columnInsertValueGlue'], $columnInsertValues)
            ));
        }
        $sql = strtr($templates['main'], array(
            '{{tableName}}' => $tableName,
            '{{columnInsertNames}}' => $columnInsertNamesSqlPart,
            '{{rowInsertValues}}' => implode($templates['rowInsertValueGlue'], $rowInsertValues),
        ));
        //condición UPDATE on Duplicate
        if (!empty($attr_uod)) {
            //preparación de 'on duplicate key'
            $columnUOD = array();
            foreach ($attr_uod as $attr) {
                if ($table->getColumn($attr) !== null) {
                    $key = $this->getDbConnection()->quoteColumnName($attr);
                    $columnUOD[] = $key . " = VALUES($key)";
                }
            }
            $columnUODSqlPart = implode($templates['columnInsertNameGlue'], $columnUOD);
            $sql_UOD = strtr($templates['addTemplate'], array(
                '{{columnUODValue}}' => $columnUODSqlPart
            ));
            $sql = $sql . $sql_UOD;
        }
        $command = $this->getDbConnection()->createCommand($sql);
        foreach ($params as $name => $value)
            $command->bindValue($name, $value);
        return $command;
    }

    /**
     * @author Qiang Xue <qiang.xue@gmail.com>
     * Checks if the parameter is a valid table schema.
     * If it is a string, the corresponding table schema will be retrieved.
     * @param mixed $table table schema ({@link CDbTableSchema}) or table name (string).
     * If this refers to a valid table name, this parameter will be returned with the corresponding table schema.
     * @throws CDbException if the table name is not valid
     */
    protected function ensureTable(&$table)
    {
        if (is_string($table) && ($table = $this->_schema->getTable($tableName = $table)) === null)
            throw new CDbException(Yii::t('yii', 'Table "{table}" does not exist.',
                array('{table}' => $tableName)));
    }
} 